package com.yfeng.common.utils;

import java.util.List;
import java.util.Map;

import com.yfeng.common.dao.Param;

public class SqlUtil {

	/**
	 * 根据params拼装insert的sql语句
	 *
	 * @param strTable
	 * @param param
	 * @return
	 */
	public static String getInsertSql(String strTable, Param param) {
		String names = "";
		String values = "";
		for (Object o : param.keySet()) {
			names += o.toString() + ",";
			values += "?" + ",";
		}
		names = names.substring(0, names.length() - 1);
		values = values.substring(0, values.length() - 1);
		return " insert into " + strTable + " (" + names + ") values (" + values + ")";
	}
	/**
	 * 根据params拼装delete的sql语句
	 *
	 * @param strTable
	 * @param param
	 * @return
	 */
	public static String getDeleteSql(String strTable, Param param) {

		String values = "";
		for (Object o : param.keySet()) {
			values += " and " + o.toString() + "=?";
		}

		return " delete from " + strTable + " where 1=1 " + values;
	}

	/**
	 * 根据params拼装update的sql语句
	 *
	 * @param strTable 表名
	 * @param param    参数数组
	 * @return SQL语句
	 */
	public static String getUpdateSql(String strTable, Param param) {
		String values = "";
		String conds = "";

		for (Object o : param.keySet()) {
			String[] m_values = o.toString().split("__");
			if (m_values[0].equals("value"))
				values += m_values[1] + "=?" + ",";
		}


		for (Object o : param.keySet()) {
			String[] m_conds = o.toString().split("__");
			if (m_conds[0].equals("cond"))
				conds += " and " + m_conds[1] + "=?";
		}
		values = values.substring(0, values.length() - 1);
		return " update " + strTable + " set " + values + " where 1=1  " + conds;

	}

	public static String getCreateSql(String strTable,List<Map> columns,boolean isPhoneBusiness){
		String sql="create table "+strTable+" (id int identity(1,1) not null,";
		for(int i=0;i<columns.size();i++){
			Map m=(Map)columns.get(i);
			sql=sql+m.get("name").toString()+" "+m.get("type").toString();
			if(m.get("type").toString().equals("varchar")){
				sql=sql+"("+m.get("length").toString()+") COLLATE Chinese_PRC_CI_AS ";
			}
			if(m.get("is_null").toString().equals("1")){
				sql=sql+" null ";
			}else{
				sql=sql+" not null ";
			}
			sql=sql+"default '"+m.get("default_value")+"',";		
		}
		if(strTable.endsWith("_his")){
			sql=sql+"SYS_PHONE 	varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,"+
			"SYS_CH int NOT NULL ,"+	
			"SYS_CUSTOMER_ID int default -1 ,"+		
			"SYS_PPHONE varchar(20) COLLATE Chinese_PRC_CI_AS default '',"+         
			"SYS_SEAT_USER_ID varchar(10) COLLATE Chinese_PRC_CI_AS default '',"+	
			"SYS_SEAT_USER_NAME varchar(50) COLLATE Chinese_PRC_CI_AS default '',"+	
			"SYS_DTMF_CHAR varchar(20) COLLATE Chinese_PRC_CI_AS default '',"+ 	
			"SYS_CALLOUT_TIME datetime default '1900-01-01',"+
			"SYS_CALLIN_RING_TIME datetime default '1900-01-01',"+ 
			"SYS_CALLTALK_TIME datetime default '1900-01-01',"+	
			"SYS_CALLHANGUP_TIME datetime default '1900-01-01',"+ 
			"SYS_CALLOUT_FAIL_TIME datetime default '1900-01-01',"+ 
			"SYS_DIALMODE int default 0,"+	                      
			"SYS_CALL_STATUS int default 0,"+                     
			"SYS_CALL_RESULT char(1) COLLATE Chinese_PRC_CI_AS default null,"+	
			"SYS_SS7  int default -1,"+		
			"SYS_PENDING int default -1,"+	
			"SYS_SAVE_TIME datetime default '1900-01-01',"+
			"SYS_INPUT_TIME datetime default null,"+ 
			"sys_INPUT_USER varchar(20) COLLATE Chinese_PRC_CI_AS default null,"+	
			"sys_updatedt datetime default null,"+
			"SYS_update_user varchar(20) COLLATE Chinese_PRC_CI_AS default null,"+	
			"SYS_CLIENT_ID varchar(20) COLLATE Chinese_PRC_CI_AS  default null,"+
			"SYS_PHONEFLAG varchar(1) COLLATE Chinese_PRC_CI_AS default null,";
			if(isPhoneBusiness){
				sql+="SYS_ISSUCCESS varchar(30) COLLATE Chinese_PRC_CI_AS default null,"+
				"SYS_GENDER varchar(10) COLLATE Chinese_PRC_CI_AS default null,"+
				"SYS_ACCEPTANCE varchar(30) COLLATE Chinese_PRC_CI_AS default null,";
			}			

			sql=sql+"constraint PK_"+strTable+"_ID primary key (ID))";
			for(int i=0;i<columns.size();i++){
				Map m=(Map)columns.get(i);
				sql=sql+"exec sp_addtable_description '"+m.get("column_desc")+"','"+strTable+"','"+m.get("name").toString()+"'";
			}
			sql=sql+"exec sp_addtable_description '电话号码','"+strTable+"','SYS_PHONE'"+
			"exec sp_addtable_description '通道号','"+strTable+"','SYS_CH'"+			
			"exec sp_addtable_description '序列号','"+strTable+"','SYS_CUSTOMER_ID'"+
			"exec sp_addtable_description '坐席分机号','"+strTable+"','SYS_PPHONE'"+
			"exec sp_addtable_description '坐席用户编号','"+strTable+"','SYS_SEAT_USER_ID'"+
			"exec sp_addtable_description '坐席用户姓名','"+strTable+"','SYS_SEAT_USER_NAME'"+
			"exec sp_addtable_description '拨打的电话号码字符','"+strTable+"','SYS_DTMF_CHAR'"+
			"exec sp_addtable_description '系统外呼时间','"+strTable+"','SYS_CALLOUT_TIME'"+
			"exec sp_addtable_description '系统振铃时间','"+strTable+"','SYS_CALLIN_RING_TIME'"+
			"exec sp_addtable_description '通话时间','"+strTable+"','SYS_CALLTALK_TIME'"+
			"exec sp_addtable_description '挂机时间','"+strTable+"','SYS_CALLHANGUP_TIME'"+
			"exec sp_addtable_description '呼叫失败时间','"+strTable+"','SYS_CALLOUT_FAIL_TIME'"+
			"exec sp_addtable_description '拨号模式0--手工外呼  1--自动外呼','"+strTable+"','SYS_DIALMODE'"+
			"exec sp_addtable_description '系统外呼状态 0--外呼，1--呼入','"+strTable+"','SYS_CALL_STATUS'"+
			"exec sp_addtable_description '呼叫结果，如果需要自动外呼将状态置为A，S--系统完成外呼，不再进行外呼 F--系统外呼失败，不再外呼 T--外呼超时失败，不再外呼 O--外呼通话中 I--呼入通话中','"+strTable+"','SYS_CALL_RESULT'"+
			"exec sp_addtable_description '失败时的7号信令结果和表DIC_CALLFAILREASON一致','"+strTable+"','SYS_SS7'"+
			"exec sp_addtable_description '电话挂机时的失败状态，通话结束成功挂机状态为0，其它失败情况参考dic_pendingreason表，注意呼叫失败的原因SS7和挂机原因不一致，如果SS7的值不为-1，那么挂机原因的值为-1，反之也是这样','"+strTable+"','SYS_PENDING'"+
			"exec sp_addtable_description '坐席修改客户数据时间','"+strTable+"','SYS_SAVE_TIME'"+
			"exec sp_addtable_description '插入时间','"+strTable+"','SYS_INPUT_TIME'"+
			"exec sp_addtable_description '插入人--AUTOUSER','"+strTable+"','sys_INPUT_USER'"+
			"exec sp_addtable_description '更新该数据的时间','"+strTable+"','sys_updatedt'"+
			"exec sp_addtable_description '更新人','"+strTable+"','SYS_update_user'"+
			"exec sp_addtable_description '客户id','"+strTable+"','SYS_CLIENT_ID'"+
			"exec sp_addtable_description '主动外呼标志,0主动外呼，1手动外呼','"+strTable+"','SYS_PHONEFLAG'";
			if(isPhoneBusiness){
				sql+="exec sp_addtable_description '销售是否成功','"+strTable+"','SYS_ISSUCCESS'"+
				"exec sp_addtable_description '性别','"+strTable+"','SYS_GENER'"+
				"exec sp_addtable_description '用户接受程度','"+strTable+"','SYS_ACCEPTANCE'";
			}
		}else if(strTable.endsWith("_record")){
			sql="create table "+strTable+
			"(ID int IDENTITY (1, 1) NOT NULL ,"+
			"USER_ID varchar(20) COLLATE Chinese_PRC_CI_AS NULL ,"+
			"FILE_NAME varchar (100)  COLLATE Chinese_PRC_CI_AS NULL ,"+
			"PHONE_NUM varchar (20) COLLATE Chinese_PRC_CI_AS  NULL ,"+
			"USER_NUM varchar (20) COLLATE Chinese_PRC_CI_AS  NULL ,"+
			"EXTENSION_NUM varchar (20) COLLATE Chinese_PRC_CI_AS  NULL ,"+
			"CALL_START_TIME datetime NULL ,"+
			"BIS_CODE int NULL ,"+
			"BIS_HIS_ID int NULL ,"+
			"RECORD_PATH varchar (255) COLLATE Chinese_PRC_CI_AS  NULL ,"+
			"DELFLAG int NULL default 0,"+
			"INPUT_TIME datetime NULL ,"+
			"INPUT_USER varchar (20) COLLATE Chinese_PRC_CI_AS  NULL ,"+
			"UPDATE_TIME datetime NULL ,"+
			"UPDATE_USER varchar (20) COLLATE Chinese_PRC_CI_AS  NULL,";

			sql=sql+"constraint PK_"+strTable+"_ID primary key (id))";
			sql=sql+"exec sp_addtable_description '坐席id','"+strTable+"','USER_ID'"+
			"exec sp_addtable_description '文件名','"+strTable+"','FILE_NAME'"+			
			"exec sp_addtable_description '主叫号码','"+strTable+"','PHONE_NUM'"+
			"exec sp_addtable_description '坐席工号','"+strTable+"','USER_NUM'"+
			"exec sp_addtable_description '员工分机号','"+strTable+"','EXTENSION_NUM'"+
			"exec sp_addtable_description '呼叫连通时间','"+strTable+"','CALL_START_TIME'"+
			"exec sp_addtable_description '业务代码','"+strTable+"','BIS_CODE'"+
			"exec sp_addtable_description '业务历史表id','"+strTable+"','BIS_HIS_ID'"+
			"exec sp_addtable_description '录音存放路径','"+strTable+"','RECORD_PATH'"+
			"exec sp_addtable_description '录音删除标志位','"+strTable+"','DELFLAG'"+
			"exec sp_addtable_description '插入时间','"+strTable+"','INPUT_TIME'"+
			"exec sp_addtable_description '插入人','"+strTable+"','INPUT_USER'"+
			"exec sp_addtable_description '更新时间','"+strTable+"','UPDATE_TIME'"+
			"exec sp_addtable_description '更新人','"+strTable+"','UPDATE_USER'";

		}else{
			sql=sql+"SYS_PHONE varchar(20) COLLATE Chinese_PRC_CI_AS NOT NULL,"+
			"SYS_USERID varchar(20) COLLATE Chinese_PRC_CI_AS default null,"+	
			"SYS_ISCALLED int default 0,"+	
			"SYS_CALL_RESULT char(1) COLLATE Chinese_PRC_CI_AS default null,"+			
			"SYS_CALLNUM int default 0,"+
			"SYS_CALLCONNECT int default 0,"+
			"SYS_LASTCALLTIME datetime default '1900-01-01',";
			if(isPhoneBusiness){
				sql+="SYS_ISSUCCESS varchar(30) COLLATE Chinese_PRC_CI_AS default null,"+
				"SYS_GENDER varchar(10) COLLATE Chinese_PRC_CI_AS default null,"+
				"SYS_ACCEPTANCE varchar(30) COLLATE Chinese_PRC_CI_AS default null,";
			}		
			sql=sql+"constraint PK_"+strTable+"_ID primary key (ID))";
			for(int i=0;i<columns.size();i++){
				Map m=(Map)columns.get(i);
				sql=sql+"exec sp_addtable_description '"+m.get("column_desc")+"','"+strTable+"','"+m.get("name").toString()+"'";
			}
			sql=sql+"exec sp_addtable_description '电话号码','"+strTable+"','SYS_PHONE'"+
			"exec sp_addtable_description '分配给坐席的id','"+strTable+"','SYS_USERID'"+
			"exec sp_addtable_description '拨打标志','"+strTable+"','SYS_ISCALLED'"+
			"exec sp_addtable_description '呼叫结果   A--设置系统自动外呼','"+strTable+"','SYS_CALL_RESULT'"+
			"exec sp_addtable_description '总呼叫次数','"+strTable+"','SYS_CALLNUM'"+
			"exec sp_addtable_description '呼叫接通次数','"+strTable+"','SYS_CALLCONNECT'"+
			"exec sp_addtable_description '最后呼叫时间','"+strTable+"','SYS_LASTCALLTIME'";
			if(isPhoneBusiness){
				sql+="exec sp_addtable_description '销售是否成功','"+strTable+"','SYS_ISSUCCESS'"+
				"exec sp_addtable_description '性别','"+strTable+"','SYS_GENER'"+
				"exec sp_addtable_description '用户接受程度','"+strTable+"','SYS_ACCEPTANCE'";
			}
		}
		return sql;
	}
	public static String getAlterSql(String strTable,List<Map> columns){


		String sql="alter table "+strTable+" add ";
		for(int i=0;i<columns.size();i++){
			Map m=(Map)columns.get(i);
			sql=sql+m.get("name").toString()+" "+m.get("type").toString();
			if(m.get("type").toString().equals("varchar")){
				sql=sql+"("+m.get("length").toString()+") COLLATE Chinese_PRC_CI_AS ";
			}
			if(m.get("is_null").toString().equals("1")){
				sql=sql+" null ";
			}else{
				sql=sql+" not null ";
			}
			sql=sql+"default '"+m.get("default_value")+"',";		
		}
		sql=sql.substring(0, sql.length()-1);
		for(int i=0;i<columns.size();i++){
			Map m=(Map)columns.get(i);
			sql=sql+" exec sp_addtable_description '"+m.get("column_desc")+"','"+strTable+"','"+m.get("name").toString()+"'";
		}
		return sql;
	}
}
